"""Re-run STEP8 rules that timed out in the main filte script.

This helper scans `VLT_LOG` for rule actions marked as `timeout`, then
replays those SQL batches using a more generous per-SQL timeout (default
15 000 seconds versus the original 1 500 seconds). Use it when the first
pass ran out of time but you now have a longer execution window.

Key notes:
- Reads the same hospital list as the main script (``hspList.json``).
- Works only on rule actions that still show `timeout` (configurable).
- Keeps all logging and database interactions consistent with
	`step8_1_filte.py`.
"""

from __future__ import annotations

import argparse
import json
import sys
import time
from collections import defaultdict
from pathlib import Path
from typing import Dict, List, Optional, Sequence, Tuple

from sqlalchemy import text

# Reuse helpers defined in the main filte module.
try:
	from step8_1_filte import (  # type: ignore
		DEBUG,
		engine,
		format_time,
		print_inline,
		print_newline,
		execute_sql_with_timeout,
	)
except ImportError as exc:  # pragma: no cover - triggered only when deployment is broken
	raise SystemExit(
		"Unable to import step8_1_filte.py; ensure both scripts share the same directory."
	) from exc


# Default timeout (seconds) for the retry pass.
DEFAULT_TIMEOUT_LIMIT = 15000


def _load_hospital_list(hsp_filter: Optional[Sequence[str]]) -> List[str]:
	"""Load the hospital list and optionally filter by name."""

	path = Path("hspList.json")
	if not path.exists():
		raise FileNotFoundError("Missing hspList.json; hospital list is unknown.")

	with path.open("r", encoding="utf-8") as fp:
		hsp_list = json.load(fp)

	hospitals = [item["hsp_abbr"] for item in hsp_list if item.get("hsp_abbr")]
	if not hospitals:
		raise ValueError("No hsp_abbr entries found in hspList.json.")

	if not hsp_filter:
		return hospitals

	target = {abbr.strip() for abbr in hsp_filter if abbr.strip()}
	filtered = [abbr for abbr in hospitals if abbr in target]
	if not filtered:
		raise ValueError("Filtered hospital list is empty; check --hsp arguments.")
	return filtered


def _collect_timeout_targets(
	statuses: Sequence[str],
	hsp_filter: Optional[Sequence[str]],
) -> Dict[str, Dict[str, List[str]]]:
	"""Collect rule acts still marked with the desired statuses."""

	if not statuses:
		return {}

	status_tokens = [status.strip() for status in statuses if status.strip()]
	if not status_tokens:
		return {}

	status_placeholders = ", ".join(
		f":status_{idx}" for idx in range(len(status_tokens))
	)
	params: Dict[str, object] = {
		f"status_{idx}": status for idx, status in enumerate(status_tokens)
	}

	where_clauses = [f"status IN ({status_placeholders})"]

	if hsp_filter:
		hsp_tokens = [abbr.strip() for abbr in hsp_filter if abbr.strip()]
		if hsp_tokens:
			hsp_placeholders = ", ".join(
				f":hsp_{idx}" for idx in range(len(hsp_tokens))
			)
			where_clauses.append(f"hsp_abbr IN ({hsp_placeholders})")
			params.update({f"hsp_{idx}": h for idx, h in enumerate(hsp_tokens)})

	where_sql = " AND ".join(where_clauses)

	query = text(
		f"""
		SELECT DISTINCT hsp_abbr, rule_id, rule_act_id
		FROM VLT_LOG
		WHERE {where_sql}
		ORDER BY hsp_abbr, rule_id, rule_act_id
		"""
	)

	targets: Dict[str, Dict[str, List[str]]] = defaultdict(lambda: defaultdict(list))

	with engine.connect() as conn:
		rows = conn.execute(query, params).fetchall()

	for hsp_abbr, rule_id, rule_act_id in rows:
		if not (hsp_abbr and rule_id and rule_act_id):
			continue
		targets[hsp_abbr][rule_id].append(rule_act_id)

	return targets


def _fetch_rule_act_key(
	hsp_abbr: str,
	rule_id: str,
	rule_act_id: str,
):
	"""Find the ruleActId key that matches the given rule_act_id value."""

	query = text(
		"""
		SELECT key FROM rule_apply
		WHERE hsp_abbr = :hsp_abbr
		  AND rule_id = :rule_id
		  AND value = :rule_act_id
		  AND key LIKE '%ruleActId%'
		ORDER BY key
		"""
	)

	with engine.connect() as conn:
		row = conn.execute(
			query,
			{
				"hsp_abbr": hsp_abbr,
				"rule_id": rule_id,
				"rule_act_id": rule_act_id,
			},
		).fetchone()

	return row[0] if row else None


def _fetch_sql_list(
	hsp_abbr: str,
	rule_id: str,
	rule_act_key: str,
) -> List[str]:
	"""Fetch ordered SQL statements bound to the given ruleAct key."""

	sql_key_prefix = rule_act_key.replace("ruleActId", "sqlList")
	query = text(
		"""
		SELECT key, sql FROM rule_apply
		WHERE hsp_abbr = :hsp_abbr
		  AND rule_id = :rule_id
		  AND key LIKE :key_pattern
		"""
	)

	with engine.connect() as conn:
		rows = conn.execute(
			query,
			{
				"hsp_abbr": hsp_abbr,
				"rule_id": rule_id,
				"key_pattern": f"{sql_key_prefix}#%",
			},
		).fetchall()

	def _extract_ord(raw_key: Optional[str]) -> int:
		if not raw_key:
			return 0
		try:
			return int(raw_key.rsplit("#", 1)[-1])
		except ValueError:
			return 0

	sql_list: List[str] = []
	for raw_key, clob_value in sorted(rows, key=lambda item: _extract_ord(item[0])):
		if clob_value is None:
			continue
		if hasattr(clob_value, "read"):
			sql_text = clob_value.read()
		else:
			sql_text = str(clob_value)
		if sql_text.strip():
			sql_list.append(sql_text)

	return sql_list


def _truncate_temp_tables(data_conn) -> None:
	temp_tables = [
		"vlt_tmp_a",
		"vlt_tmp_b",
		"vlt_tmp_b2",
		"vlt_tmp_b3",
		"vlt_tmp_b4",
		"vlt_tmp_b5",
	]
	for table_name in temp_tables:
		try:
			data_conn.execute(text(f"TRUNCATE TABLE {table_name}"))
		except Exception as exc:
			if DEBUG:
				raise
			print_newline(f"Warning: truncate {table_name} failed; continuing - {exc}")
	data_conn.commit()


def _run_rule_act(
	hsp_abbr: str,
	rule_id: str,
	rule_act_id: str,
	sql_list: Sequence[str],
	timeout_limit: int,
) -> Tuple[int, int, int, bool]:
	"""Run a single rule_act with the extended timeout limit."""

	successes = 0
	failures = 0
	timeouts = 0
	skipped = False

	with engine.connect() as log_conn, engine.connect() as data_conn:
		_truncate_temp_tables(data_conn)

		for sql_ord, sql in enumerate(sql_list):
			msg = (
				f"[{hsp_abbr}] rule {rule_id} act {rule_act_id} "
				f"SQL{sql_ord + 1}/{len(sql_list)}"
			)
			print_inline(msg)

			result, error, _ = execute_sql_with_timeout(
				sql,
				sql_ord,
				hsp_abbr,
				rule_id,
				rule_act_id,
				log_conn=log_conn,
				data_conn=data_conn,
				timeout_seconds=timeout_limit,
			)

			log_conn.commit()

			if error:
				if "timeout" in error.lower():
					timeouts += 1
				else:
					failures += 1
				if DEBUG:
					print_newline(f"[DEBUG] SQL execution failed: {error}")
					raise RuntimeError(error)
				data_conn.rollback()
				skipped = True
				break

			successes += 1
			data_conn.commit()

	return successes, failures, timeouts, skipped


def process_hospital_timeouts(
	hsp_abbr: str,
	targets: Dict[str, List[str]],
	timeout_limit: int,
) -> Tuple[int, int, int, int]:
	"""Re-run all target rule_acts for a single hospital.""" 

	start = time.time()
	total_rule_acts = sum(len(rule_act_ids) for rule_act_ids in targets.values())

	processed = 0
	skipped = 0
	successes = 0
	failures = 0
	timeouts = 0

	for rule_id, rule_act_ids in targets.items():
		for rule_act_id in rule_act_ids:
			processed += 1
			rule_act_key = _fetch_rule_act_key(hsp_abbr, rule_id, rule_act_id)
			if not rule_act_key:
				print_newline(
					f"Skip {hsp_abbr} - {rule_id} - {rule_act_id}: missing ruleActId key."
				)
				skipped += 1
				continue

			sql_list = _fetch_sql_list(hsp_abbr, rule_id, rule_act_key)
			if not sql_list:
				print_newline(
					f"Skip {hsp_abbr} - {rule_id} - {rule_act_id}: no SQL statements found."
				)
				skipped += 1
				continue

			succ, fail, tout, rule_skipped = _run_rule_act(
				hsp_abbr,
				rule_id,
				rule_act_id,
				sql_list,
				timeout_limit,
			)

			successes += succ
			failures += fail
			timeouts += tout
			if rule_skipped:
				skipped += 1

			elapsed = format_time(time.time() - start)
			print_newline(
				f"[{hsp_abbr}] processed {processed}/{total_rule_acts} rule_act"
				f" - success {successes} fail {failures} timeout {timeouts} elapsed {elapsed}"
			)

	return successes, failures, timeouts, skipped


def parse_args(argv: Optional[Sequence[str]] = None) -> argparse.Namespace:
	parser = argparse.ArgumentParser(description="Re-run STEP8 rule_acts that timed out.")
	parser.add_argument(
		"--hsp",
		nargs="*",
		help="Only process the specified hospitals (space separated).",
	)
	parser.add_argument(
		"--statuses",
		nargs="*",
		default=["timeout"],
		help="VLT_LOG statuses that should be retried (default: timeout).",
	)
	parser.add_argument(
		"--timeout",
		type=int,
		default=DEFAULT_TIMEOUT_LIMIT,
		help="Timeout per SQL in seconds (default 15000).",
	)
	return parser.parse_args(argv)


def main(argv: Optional[Sequence[str]] = None) -> None:
	args = parse_args(argv)

	try:
		hospitals = _load_hospital_list(args.hsp)
	except Exception as exc:
		raise SystemExit(str(exc)) from exc

	targets = _collect_timeout_targets(args.statuses, args.hsp)
	if not targets:
		print_newline("No matching rule_act entries found; nothing to re-run.")
		return

	timeout_limit = max(args.timeout, 1)

	print_newline("=" * 80)
	print_newline("STEP8 filte retry - filte_more")
	print_newline(f"Timeout per SQL: {timeout_limit} s")
	print_newline("=" * 80)

	total_success = 0
	total_fail = 0
	total_timeout = 0
	total_skipped = 0

	overall_start = time.time()

	for hsp_abbr in hospitals:
		hospital_targets = targets.get(hsp_abbr)
		if not hospital_targets:
			continue

		print_newline("-" * 80)
		print_newline(f"Hospital: {hsp_abbr}")

		succ, fail, tout, skipped = process_hospital_timeouts(
			hsp_abbr,
			hospital_targets,
			timeout_limit,
		)

		total_success += succ
		total_fail += fail
		total_timeout += tout
		total_skipped += skipped

	elapsed = format_time(time.time() - overall_start)

	print_newline("=" * 80)
	print_newline(
		f"Retry done - SQL success {total_success} fail {total_fail} "
		f"still timeout {total_timeout} skipped {total_skipped}"
	)
	print_newline(f"Total elapsed: {elapsed}")
	print_newline("=" * 80)


if __name__ == "__main__":
	main(sys.argv[1:])
